﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using GovernmentOfficeProject.SysTools;
using GovernmentOfficeProject.LevelDataAccessModel;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace GovernmentOfficeProject.LevelDataAccess
{
    public class DAL_COM_Sms
    {

        /// <summary>
        /// 根据smsID,获取SMS实体.
        /// </summary>
        /// <param name="smsID"></param>
        /// <returns></returns>
        public Entity_COM_Sms getSmsBySmsID(int smsID)
        {
            try
            {
                string strSQL = "select * from [COM_SMS] where id=@smsID";
                SqlParameter[] values = new SqlParameter[]{
                    new SqlParameter("@smsID",smsID)
                };
                DataSet dst = DAL_DBGM.GetDataSet(strSQL,values);
                Entity_COM_Sms sms = null;
                foreach (DataRow row in dst.Tables[0].Rows)
                {
                    sms = new Entity_COM_Sms();
                    sms.SmsID = Convert.ToInt32(row["id"]);
                    sms.SmsMtNumber = Convert.ToString(row["Sms_MtNumber"]);
                    sms.SmsCaseNo = Convert.ToString(row["Sms_Case_No"]);
                    sms.SmsCaseApplicant = Convert.ToString(row["Sms_CaseApplicant"]);
                    sms.SmsCaseServiceNO = Convert.ToInt32(row["Sms_Case_ServiceNO"]);
                    sms.SmsSendTime = Convert.ToString(row["Sms_SendTime"]);
                    sms.SmsText = Convert.ToString(row["Sms_Text"]);
                    sms.SmsSendFlag = Convert.ToInt32(row["Sms_SendFlag"]);
                }
                return sms;
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_COM-Sms.getSmsBySmsID(int smsID)---", SysTools.AppLog.LogMessageType.Error);
                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public List<Entity_COM_Sms> getSmsList()
        {
            try
            {
                string strSQL = "select * from [COM_SMS]";
                DataSet dst = DAL_DBGM.GetDataSet(strSQL);
                Entity_COM_Sms sms = null;
                List<Entity_COM_Sms> smsList = new List<Entity_COM_Sms>();
                foreach (DataRow row in dst.Tables[0].Rows)
                {
                    sms = new Entity_COM_Sms();
                    sms.SmsID = Convert.ToInt32(row["id"]);
                    sms.SmsMtNumber = Convert.ToString(row["Sms_MtNumber"]);
                    sms.SmsCaseNo = Convert.ToString(row["Sms_Case_No"]);
                    sms.SmsCaseApplicant = Convert.ToString(row["Sms_CaseApplicant"]);
                    sms.SmsCaseServiceNO = Convert.ToInt32(row["Sms_Case_ServiceNO"]);
                    sms.SmsSendTime = Convert.ToString(row["Sms_SendTime"]);
                    sms.SmsText = Convert.ToString(row["Sms_Text"]);
                    sms.SmsSendFlag = Convert.ToInt32(row["Sms_SendFlag"]);
                    smsList.Add(sms);
                }
                return smsList;
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_COM-Sms.getSmsList()---", SysTools.AppLog.LogMessageType.Error);
                throw ex;
            }
        }

        /// <summary>
        /// 根据传入的sms实体条件,获取SMS列表.
        /// </summary>
        /// <param name="smsEntity"></param>
        /// <returns></returns>
        public List<Entity_COM_Sms> getSmsListBySmsEntity(Entity_COM_Sms smsEntity)
        {
            try
            {
                string strSQL = "select * from [COM_SMS] where 1=1";
                if (smsEntity.SmsMtNumber!="" && smsEntity.SmsMtNumber!=null)
                {
                    strSQL += " and Sms_MtNumber=" + smsEntity.SmsMtNumber;
                }
                if (smsEntity.SmsCaseNo!= "" && smsEntity.SmsCaseNo!=null)
                {
                    strSQL += " and Sms_Case_No=" + smsEntity.SmsCaseNo;
                }
                if (smsEntity.SmsCaseApplicant!="" && smsEntity.SmsCaseApplicant!=null)
                {
                    strSQL += " and Sms_Case_Applicat=" + smsEntity.SmsCaseApplicant;
                }
                if (smsEntity.SmsCaseServiceNO != -1)
                {
                    strSQL += " and Sms_Case_ServiceNo=" + smsEntity.SmsCaseServiceNO;
                }
                if (smsEntity.SmsSendTime!="" && smsEntity.SmsSendTime!=null)
                {
                    strSQL += " and Sms_SendTime=" + smsEntity.SmsSendTime;
                }
                if (smsEntity.SmsText!="" && smsEntity.SmsText!=null)
                {
                    strSQL += " and Sms_Text=" + smsEntity.SmsText;
                }
                if (smsEntity.SmsSendFlag != -1)
                {
                    strSQL += " and Sms_SendFlag=" + smsEntity.SmsSendFlag;
                }
                DataSet dst = DAL_DBGM.GetDataSet(strSQL);
                Entity_COM_Sms sms = null;
                List<Entity_COM_Sms> smsList = new List<Entity_COM_Sms>();
                foreach (DataRow row in dst.Tables[0].Rows)
                {
                    sms = new Entity_COM_Sms();
                    sms.SmsID = Convert.ToInt32(row["id"]);
                    sms.SmsMtNumber = Convert.ToString(row["Sms_MtNumber"]);
                    sms.SmsCaseNo = Convert.ToString(row["Sms_Case_No"]);
                    sms.SmsCaseApplicant = Convert.ToString(row["Sms_CaseApplicant"]);
                    sms.SmsCaseServiceNO = Convert.ToInt32(row["Sms_Case_ServiceNO"]);
                    sms.SmsSendTime = Convert.ToString(row["Sms_SendTime"]);
                    sms.SmsText = Convert.ToString(row["Sms_Text"]);
                    sms.SmsSendFlag = Convert.ToInt32(row["Sms_SendFlag"]);
                    smsList.Add(sms);
                }
                return smsList;
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_COM-Sms.getSmsListBySmsEntity(Entity_COM_Sms smsEntity)---", SysTools.AppLog.LogMessageType.Error);
                throw ex;
            }
        }

        /// <summary>
        /// 根据传入的sms实体条件和信息发送时间的间隔,获取SMS列表.
        /// </summary>
        /// <param name="smsEntity"></param>
        /// <returns></returns>
        public List<Entity_COM_Sms> getSmsListBySmsEntityAndSendTimeStartEnd(Entity_COM_Sms smsEntity,string startTime,string endTime)
        {
            try
            {
                string strSQL = "select * from [COM_SMS] where 1=1";
                if (smsEntity.SmsMtNumber != "" && smsEntity.SmsMtNumber != null)
                {
                    strSQL += " and Sms_MtNumber=" + smsEntity.SmsMtNumber;
                }
                if (smsEntity.SmsCaseNo != "" && smsEntity.SmsCaseNo != null)
                {
                    strSQL += " and Sms_Case_No=" + smsEntity.SmsCaseNo;
                }
                if (smsEntity.SmsCaseApplicant != "" && smsEntity.SmsCaseApplicant != null)
                {
                    strSQL += " and Sms_Case_Applicat=" + smsEntity.SmsCaseApplicant;
                }
                if (smsEntity.SmsCaseServiceNO != -1)
                {
                    strSQL += " and Sms_Case_ServiceNo=" + smsEntity.SmsCaseServiceNO;
                }
                if (smsEntity.SmsSendTime != "" && smsEntity.SmsSendTime != null)
                {
                    strSQL += " and Sms_SendTime between convert(DateTime,'" + Convert.ToDateTime(startTime).ToString() + "') and convert(DateTime,'" + Convert.ToDateTime(endTime).ToString() + "')";
                }
                if (smsEntity.SmsText != "" && smsEntity.SmsText != null)
                {
                    strSQL += " and Sms_Text=" + smsEntity.SmsText;
                }
                if (smsEntity.SmsSendFlag != -1)
                {
                    strSQL += " and Sms_SendFlag=" + smsEntity.SmsSendFlag;
                }
                AppLog.Write("根据传入的sms实体条件和信息发送时间的间隔,获取SMS列表.的SQL语句:" + strSQL, AppLog.LogMessageType.Debug);
                DataSet dst = DAL_DBGM.GetDataSet(strSQL);
                Entity_COM_Sms sms = null;
                List<Entity_COM_Sms> smsList = new List<Entity_COM_Sms>();
                foreach (DataRow row in dst.Tables[0].Rows)
                {
                    sms = new Entity_COM_Sms();
                    sms.SmsID = Convert.ToInt32(row["id"]);
                    sms.SmsMtNumber = Convert.ToString(row["Sms_MtNumber"]);
                    sms.SmsCaseNo = Convert.ToString(row["Sms_Case_No"]);
                    sms.SmsCaseApplicant = Convert.ToString(row["Sms_CaseApplicant"]);
                    sms.SmsCaseServiceNO = Convert.ToInt32(row["Sms_Case_ServiceNO"]);
                    sms.SmsSendTime = Convert.ToString(row["Sms_SendTime"]);
                    sms.SmsText = Convert.ToString(row["Sms_Text"]);
                    sms.SmsSendFlag = Convert.ToInt32(row["Sms_SendFlag"]);
                    smsList.Add(sms);
                }
                return smsList;
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_COM-Sms.getSmsListBySmsEntity(Entity_COM_Sms smsEntity)---", SysTools.AppLog.LogMessageType.Error);
                throw ex;
            }
        }

        /// <summary>
        /// 添加一条SMS信息.
        /// </summary>
        /// <param name="smsEntity"></param>
        /// <returns></returns>
        public bool addSms(Entity_COM_Sms smsEntity)
        {
            try
            {
                bool flag = false;
                bool blState = false;
                StringBuilder sqlBudIns = new StringBuilder();
                StringBuilder sqlBudValue = new StringBuilder();
                sqlBudIns.Append("insert into [COM_SMS](");
                sqlBudValue.Append(" values(");
                List<SqlParameter> sprList = new List<SqlParameter>();

                if (smsEntity.SmsMtNumber != "" && smsEntity.SmsMtNumber != null)
                {
                    sqlBudIns.Append("Sms_MtNumber,");
                    sqlBudValue.Append("@mtNumber,");
                    sprList.Add(new SqlParameter("@mtNumber", smsEntity.SmsMtNumber));
                    blState = true;
                }
                if (smsEntity.SmsCaseNo != "" && smsEntity.SmsCaseNo!= null)
                {
                    sqlBudIns.Append("Sms_Case_No,");
                    sqlBudValue.Append("@caseNo,");
                    sprList.Add(new SqlParameter("@caseNo", smsEntity.SmsCaseNo));
                    blState = true;
                }
                if (smsEntity.SmsCaseApplicant != "" && smsEntity.SmsCaseApplicant != null)
                {
                    sqlBudIns.Append("Sms_Case_Applicant,");
                    sqlBudValue.Append("@caseApplicant,");
                    sprList.Add(new SqlParameter("@caseApplicant", smsEntity.SmsCaseApplicant));
                    blState = true;
                }
                if (smsEntity.SmsCaseServiceNO != -1)
                {
                    sqlBudIns.Append("Sms_Case_ServiceNO,");
                    sqlBudValue.Append("@caseServiceNo,");
                    sprList.Add(new SqlParameter("@caseServiceNo", smsEntity.SmsCaseServiceNO));
                    blState = true;
                }
                if (smsEntity.SmsSendTime != "" && smsEntity.SmsSendTime != null)
                {
                    sqlBudIns.Append("Sms_SendTime,");
                    sqlBudValue.Append("@sendTime,");
                    sprList.Add(new SqlParameter("@sendTime", smsEntity.SmsSendTime));
                    blState = true;
                }
                if (smsEntity.SmsText != "" && smsEntity.SmsText != null)
                {
                    sqlBudIns.Append("Sms_Text,");
                    sqlBudValue.Append("@text,");
                    sprList.Add(new SqlParameter("@text", smsEntity.SmsText));
                    blState = true;
                }
                if (smsEntity.SmsSendFlag != -1)
                {
                    sqlBudIns.Append("Sms_SendFlag,");
                    sqlBudValue.Append("@sendFlag,");
                    sprList.Add(new SqlParameter("@sendFlag", smsEntity.SmsSendFlag));
                    blState = true;
                }

                if (blState)
                {
                    string strSQL = sqlBudIns.ToString().Substring(0, sqlBudIns.Length - 1) + ")" + sqlBudValue.ToString().Substring(0, sqlBudValue.Length - 1) + ")";
                    SqlParameter[] values = sprList.ToArray();
                    AppLog.Write("DAL-增加SMS的SQL语句: " + strSQL, AppLog.LogMessageType.Debug);
                    flag = DAL_DBGM.InsertInfo(strSQL, values);
                }
                else
                {
                    //添加用户失败:没有任何数据.
                    AppLog.Write("添加SMS失败:没有任何数据.", AppLog.LogMessageType.Info);
                }
                return flag;
                
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_COM-Sms.addSms(Entity_COM_Sms smsEntity)---", SysTools.AppLog.LogMessageType.Error);
                throw ex;
            }
        }

        /// <summary>
        /// 根据ID删除一条SMS.
        /// </summary>
        /// <param name="smsID"></param>
        /// <returns></returns>
        public bool deleteSmsBySmsID(int smsID)
        {
            try
            {
                string strSQL = "delete from [COM_SMS] where id=@smsID";
                SqlParameter[] values = new SqlParameter[]{
                    new SqlParameter("@smsID",smsID)
                };
                return DAL_DBGM.DeleteInfo(strSQL, values);
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_COM-Sms.deleteSmsBySmsID(int smsID)---", SysTools.AppLog.LogMessageType.Error);
                throw ex;
            }
        }


        ///// <summary>
        ///// 根据id,更新短信,暂且不需要.
        ///// </summary>
        ///// <param name="smsID"></param>
        ///// <param name="smsEntity"></param>
        ///// <returns></returns>
        //public bool updateSmsBySmsIDandSmsEntity(int smsID,Entity_COM_Sms smsEntity)
        //{                    
        //    try
        //    {
        //        return false;
        //    }
        //    catch (System.Exception ex)
        //    {
        //        AppLog.Write("---DAL_COM-Sms.updateSmsBySmsIDandSmsEntity(int smsID,Entity_COM_Sms smsEntity)---", SysTools.AppLog.LogMessageType.Error);
        //        throw ex;
        //    }
        //}

    }
}
